﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using studentManagementSystemModels;
using bug;
using System.Windows.Forms;

namespace myschoolDal
{
    /// <summary>
    /// 提供学生的数据层交互
    /// </summary>
    public class stuservers
    {
        public static string strcon = "Data source = .; Initial Catalog = myschool;Integrated Security = true";

        #region 检查学生登录信息
        /// <summary>
        /// 检查学生登录信息是否存在，密码是否正确
        /// </summary>
        /// <param name="stuLoginId"></param>
        /// <param name="stuLoginPassword"></param>
        /// <returns> true 账号存在，密码正确； false 账号不存在或者密码不正确</returns>
        public static bool StuLoginCheck(long stuLoginId, string stuLoginPassword)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    //设置数据库语句
                    string sqlcheckidpassword = "SELECT stuid,stupassword FROM student WHERE stuid = '" + stuLoginId + "' AND stupassword = '" + stuLoginPassword + "'";
                    //在SQL数据库查询相关信息
                    SqlDataAdapter adp = new SqlDataAdapter(sqlcheckidpassword, conn);
                    DataSet ds = new DataSet();
                    ds.Clear();
                    //填充相关信息
                    adp.Fill(ds);

                    //如果查询到相关信息，即ds.Tables[0].Rows.Count不为0 返回TRUE，反之返回FALSE
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        //关闭数据库
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 获取密码
        /// <summary>
        /// 已知学生学号获取学生密码
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static string GetStuInformationPsd(long ID)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    string psd;

                    //数据库查询语句
                    string sql = "select stupassword from student where stuid =" + ID + ";";
                    SqlCommand cmd = new SqlCommand(sql, conn);

                    psd = cmd.ExecuteScalar().ToString();

                    return psd;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        //关闭数据库
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 学生修改密码
        /// <summary>
        /// 修改学生密码
        /// </summary>
        /// <param name="newstuPassword_1"></param>
        /// <param name="newStuPassword_2"></param>
        public static void StuChangePassword(string newstuPassword, long ID)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    string sqlchagestupassword = "update student set stupassword ='" + newstuPassword + "' where stuid = '" + ID + "'";
                    SqlDataAdapter adp = new SqlDataAdapter(sqlchagestupassword, conn);
                    cmd.CommandText = sqlchagestupassword;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        //关闭数据库
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 检查id
        /// <summary>
        /// 检查学生id是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool CheckID(long id)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    //SqlCommand cmd = new SqlCommand("SELECT stuid,stupassword FROM stu WHERE stuid = '" + stuLoginId + "' AND stupassword = " + stuLoginPassword, conn);
                    string sqlcheckidpassword = "SELECT stuid FROM student WHERE stuid = '" + id + "';";
                    SqlDataAdapter adp = new SqlDataAdapter(sqlcheckidpassword, conn);
                    DataSet ds = new DataSet();
                    ds.Clear();
                    adp.Fill(ds);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        //关闭数据库
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }

        #endregion

        #region 检查密码
        /// <summary>
        /// 检查密码是否正确
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckPasswd(string passwd)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                    conn.Dispose();
                };
                try
                {
                    //SqlCommand cmd = new SqlCommand("SELECT stuid,stupassword FROM stu WHERE stuid = '" + stuLoginId + "' AND stupassword = " + stuLoginPassword, conn);

                    string sqlcheckidpassword = "SELECT stuid,stupassword FROM student WHERE stuid = '" + passwd + "'";
                    SqlDataAdapter adp = new SqlDataAdapter(sqlcheckidpassword, conn);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return false;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region 展示学生个人信息
        //public List <stu> GetStus()
        //{
        //    List<stu> stus = new List<stu>();



        //    return stus;
        //}


        #endregion

        #region 查询成绩
        /// <summary>
        /// 已知学生学号，获取某学期所有课程成绩
        /// </summary>
        /// <param name="term"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataSet StuGetScores(string term, long ID)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();

                };
                try
                {
                    DataSet ds = new DataSet();

                    //数据库语句
                    string Sql = "select DISTINCT scores as 课程, clssaname as 课程名, term as 学期 from  sc, class where sc.stuid = " + ID + " and term = '" + term + "' and class.classid = sc.classid";

                    SqlDataAdapter adp = new SqlDataAdapter(Sql, conn);
                    adp.Fill(ds);

                    return ds;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region 显示课程表
        /// <summary>
        /// 获取学生课表
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataSet StuShowMyClasssChedule(long ID)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    DataSet ds = new DataSet();

                    string sql = "select stuid from student where stuid = '" + ID + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    String id1 = cmd.ExecuteScalar().ToString();
                    int stuid = 0;
                    int.TryParse(id1, out stuid);
                    //用到两个数据库的连接操作

                    sql = "select class.classid as 课程id,class.clssaname as 课程名称,class.teacher as 教师姓名,class.term as 学期,classdetail.sctime as 上课时间,classdetail.location as 上课地点 from sc,classdetail,class where class.classid=sc.classid and sc.classid=classdetail.classid and sc.stuid=" + ID;
                    SqlDataAdapter adp1 = new SqlDataAdapter(sql, conn);
                    adp1.Fill(ds);
                    //载入基本信息

                    return ds;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region  选课中心

        #region 学生已经选择的课程
        /// <summary>
        /// 获取学生已选择的课程
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataSet StuGetAllSelectSouce(long ID)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    DataSet ds = new DataSet();

                    string sql = "select stuid from student where stuid = '" + ID + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    String id1 = cmd.ExecuteScalar().ToString();
                    int stuid = 0;
                    int.TryParse(id1, out stuid);
                    //用到两个数据库的连接操作

                    sql = "select class.clssaname  from class, sc where  sc.stuid=" + ID + "and class.classid = sc.classid";
                    SqlDataAdapter adp1 = new SqlDataAdapter(sql, conn);
                    adp1.Fill(ds);
                    //载入基本信息
                    return ds;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region 所有可以选择的课程
        /// <summary>
        /// 显示某学期下，可选择的专业
        /// </summary>
        /// <param name="term"></param>
        /// <returns></returns>
        public static DataSet StuGetAllSelectSouce(string term)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    DataSet ds = new DataSet();

                    string sql = "select clssaname as 课程 from class where term = '" + term + "'";

                    SqlDataAdapter adp1 = new SqlDataAdapter(sql, conn);
                    adp1.Fill(ds);
                    //载入基本信息

                    return ds;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region 添加选课
        /// <summary>
        /// 添加学生选课
        /// </summary>
        /// <param name="source"></param>
        /// <param name="term"></param>
        /// <param name="stuid"></param>
        public static void StuAddSource(string source, string term, long stuid)
        {
            using (SqlConnection conn = new SqlConnection(stuservers.strcon))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //打开数据库
                    conn.Open();
                };
                try
                {
                    int num;
                    string sql1 = "select max(scid) from sc";

                    SqlCommand cmd = new SqlCommand(sql1, conn);

                    num = cmd.ExecuteNonQuery();

                    num++;

                    //MessageBox.Show(num.ToString());

                    string sql = "insert into sc(scid, classid, stuid, scores) values(" + num + "," + manserver.GetClassInformationID(source, term) + "," + stuid + ", -1)";

                    cmd.CommandText = sql;

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #endregion
    }
}
